<?php
/**
 * 创建完整数据库结构SQL文件
 * 在根目录下运行: php database/generate_complete_schema.php
 */

// 数据库配置
$dbConfig = [
    'host' => '101.35.252.112',
    'port' => '45508',
    'database' => 'trace',
    'username' => 'root',
    'password' => 'CeyZFHxjPABhp2Hz',
    'charset' => 'utf8mb4',
    'prefix' => 'gf_'
];

try {
    // 创建PDO连接
    $dsn = "mysql:host={$dbConfig['host']};port={$dbConfig['port']};dbname={$dbConfig['database']};charset={$dbConfig['charset']}";
    $pdo = new PDO($dsn, $dbConfig['username'], $dbConfig['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
    
    echo "数据库连接成功\n";
    
    // 获取所有表结构
    $tables = $pdo->query("SHOW TABLES LIKE 'gf_%'")->fetchAll(PDO::FETCH_COLUMN);
    
    $completeSql = "-- XAppTool 完整数据库结构\n";
    $completeSql .= "-- 生成时间: " . date('Y-m-d H:i:s') . "\n";
    $completeSql .= "-- 数据库: {$dbConfig['database']}\n";
    $completeSql .= "-- 表前缀: {$dbConfig['prefix']}\n\n";
    
    // 如果会员表不存在，添加会员系统表
    if (!in_array('gf_membership_plans', $tables)) {
        $completeSql .= "-- 会员系统表（如果不存在则创建）\n";
        $completeSql .= file_get_contents(__DIR__ . '/migrations/20251118_membership_billing_with_prefix.sql');
        $completeSql .= "\n\n";
    }
    
    // 获取现有表结构
    foreach ($tables as $table) {
        echo "正在导出表: $table\n";
        
        // 获取建表语句
        $result = $pdo->query("SHOW CREATE TABLE `$table`")->fetch(PDO::FETCH_ASSOC);
        $createTableSql = $result['Create Table'];
        
        // 添加注释
        $completeSql .= "-- ----------------------------\n";
        $completeSql .= "-- Table structure for `$table`\n";
        $completeSql .= "-- ----------------------------\n";
        $completeSql .= "DROP TABLE IF EXISTS `$table`;\n";
        $completeSql .= $createTableSql . ";\n\n";
        
        // 获取表数据（只获取重要配置表的数据）
        if (in_array($table, ['gf_membership_plans', 'gf_payment_config', 'gf_system_config'])) {
            $rows = $pdo->query("SELECT * FROM `$table`")->fetchAll(PDO::FETCH_ASSOC);
            if (!empty($rows)) {
                $completeSql .= "-- ----------------------------\n";
                $completeSql .= "-- Records for `$table`\n";
                $completeSql .= "-- ----------------------------\n";
                
                foreach ($rows as $row) {
                    $columns = array_keys($row);
                    $values = array_map(function($value) use ($pdo) {
                        if ($value === null) return 'NULL';
                        return $pdo->quote($value);
                    }, array_values($row));
                    
                    $completeSql .= "INSERT INTO `$table` (`" . implode('`, `', $columns) . "`) VALUES (" . implode(', ', $values) . ");\n";
                }
                $completeSql .= "\n";
            }
        }
    }
    
    // 添加其他重要表的索引信息
    $completeSql .= "-- ----------------------------\n";
    $completeSql .= "-- Additional Indexes\n";
    $completeSql .= "-- ----------------------------\n";
    
    // 会员系统相关索引
    $completeSql .= "CREATE INDEX IF NOT EXISTS idx_user_membership_active ON gf_user_memberships(user_id, status, end_date);\n";
    $completeSql .= "CREATE INDEX IF NOT EXISTS idx_billing_date ON gf_billing_records(user_id, created_at);\n";
    $completeSql .= "CREATE INDEX IF NOT EXISTS idx_balance_updated ON gf_user_balances(user_id, updated_at);\n";
    
    // 写入完整SQL文件
    $outputFile = __DIR__ . '/complete_schema.sql';
    file_put_contents($outputFile, $completeSql);
    
    echo "\n✅ 完整数据库结构SQL文件已生成: $outputFile\n";
    echo "📁 文件大小: " . number_format(strlen($completeSql) / 1024, 2) . " KB\n";
    
} catch (PDOException $e) {
    echo "❌ 数据库连接失败: " . $e->getMessage() . "\n";
    exit(1);
} catch (Exception $e) {
    echo "❌ 执行失败: " . $e->getMessage() . "\n";
    exit(1);
}

echo "\n🎉 数据库结构导出完成！\n";